package net.esj.basic.dao.jdbc.jpa.support;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import net.esj.basic.dao.jdbc.jpa.Query;
import net.esj.basic.dao.jdbc.jpa.ResultSetExtractor;
import net.esj.basic.dao.jdbc.jpa.Session;
import net.esj.basic.dao.jdbc.jpa.TableBeanMapping;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 目前仅仅针对mysql的实现。
 */
public abstract class QuerySupport implements Query {
	  private static final Log logger = 
		  LogFactory.getLog(QuerySupport.class);
	//常用的结果集抽取类。
	public final static MapResultSetExtractor mapResultSetExtractor = new MapResultSetExtractor();
	public final static  SingleColumnResultSetExtractor singleColumnResultSetExtractor = new SingleColumnResultSetExtractor();
	
	protected Session session;
	protected String sql;
	protected Integer maxResults;
	protected Integer firstResult = 0;
	protected List<Parameter> params = new ArrayList<Parameter>();
	protected static class Parameter{
		public int position;
		public Object value;
	}
	
	public QuerySupport() {
	}

	public QuerySupport(String sql) {
		this.sql = sql;
	}

	public int executeUpdate() {
		try {
			if(logger.isDebugEnabled())
				logger.debug(sql);
			PreparedStatement pstat = session.getConnection().prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			for (Parameter p : params) {
				if(p.value instanceof Timestamp){
					pstat.setTimestamp(p.position, (Timestamp)p.value);
				}else if(p.value instanceof java.sql.Date){
					pstat.setDate(p.position, (java.sql.Date)p.value);
				}else if(p.value instanceof Date){
					pstat.setDate(p.position, new java.sql.Date(((Date)p.value).getTime()));
				}else
				pstat.setObject(p.position, p.value);
			}
			return pstat.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(),e);
		}
	}
	/**
	 * 从原始sql包装出一个分页的sql。
	 * 如mysql "select * from ("+sql+") limit "+this.firstResult+","+this.maxResults;
	 * @param sql
	 * @return
	 */
	public abstract String composePaginationSql(String sql);
	/**
	 * 这个实现仅仅是针对mysql可以。
	 */
	public List getResultList(ResultSetExtractor extractor) {
		ResultSet rs = null;
		String sqlx = sql;
		try {
			if(this.maxResults != null){
				sqlx = composePaginationSql(sql);
			}
			if(logger.isDebugEnabled())
				logger.debug(sqlx);
			PreparedStatement pstat = session.getConnection().prepareStatement(sqlx);
			for (Parameter p : params) {
				pstat.setObject(p.position, p.value);
			}
			rs = pstat.executeQuery();
			List rsList = new ArrayList();
			while(rs.next()){
				Object m = extractor.extractData(rs);
				rsList.add(m);
			}
			return rsList;
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(),e);
		}finally{
			if(rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error(e.getMessage()+sqlx, e);
				}
		}
	}

	public Object getSingleResult(ResultSetExtractor extractor) {
		ResultSet rs = null;
		try {
			if(logger.isDebugEnabled())
				logger.debug(sql);
			PreparedStatement pstat = session.getConnection().prepareStatement(sql);
			for (Parameter p : params) {
				pstat.setObject(p.position, p.value);
			}
			rs = pstat.executeQuery();
			if(rs.next())return extractor.extractData(rs);
			return null;
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(),e);
		}finally{
			if(rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error(e.getMessage()+sql, e);
				}
		}
	}
	public List getResultListAs(Class entityType) {
		TableBeanMapping mapping = session.getSessionFactory().getTableBeanMapping(entityType);
		JPABeanResultSetExtractor extractor = new JPABeanResultSetExtractor(entityType, mapping);
		return getResultList(extractor);
	}

	public Object getSingleResultAs(Class entityType) {
		TableBeanMapping mapping = session.getSessionFactory().getTableBeanMapping(entityType);
		JPABeanResultSetExtractor extractor = new JPABeanResultSetExtractor(entityType, mapping);
		return getSingleResult(extractor);
	}
	public List getResultListAsMap() {
		return getResultList(mapResultSetExtractor);
	}

	public Object getSingleResultAsMap() {
		return getSingleResult(mapResultSetExtractor);
	}
	public Query setFirstResult(int firstResult) {
		this.firstResult = firstResult;
		return this;
	}
	public Query setMaxResults(int maxResults) {
		this.maxResults = maxResults;
		return this;
	}
	public Query setParameter(String s, Object obj) {
		// TODO Query setParameter(String s, Object obj)
		return null;
	}
	public Query setParameter(int i, Object obj) {
		Parameter p = new Parameter();
		p.position = i;
		p.value = obj;
		this.params.add(p);
		return this;
	}

	public String getSql() {
		return sql;
	}

	public void setSql(String sql) {
		this.sql = sql;
	}

	public Session getSession() {
		return session;
	}

	public void setSession(Session session) {
		this.session = session;
	}
	public Object getSingleColumnResult() {
		return getSingleResult(singleColumnResultSetExtractor);
	}
	public List getSingleColumnResultList() {
		return this.getResultList(singleColumnResultSetExtractor);
	}
}
